--Function of adding a role
create or replace procedure meta_add_role(
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
begin
    select count(*) into a_row_count
    from meta_roles
    where role_name = lower(a_role_name);
    
    if a_row_count > 0 then
        raise_application_error(-20034,
                'A role with the specified name was already added (role_name = '
                || a_role_name
                || ') (#error_code = 20034)');
    end if;

    begin
        --Creating a role
        execute immediate 'create role ' || a_role_name;
    exception when others then
        --If a role with the specified name already exists
        select 1 into a_row_count from dual; --Doing nothing
    end;

    --Updating the metadata
    insert into meta_roles (role_name)
    values(lower(a_role_name));    
end;

--go

--Function of checking role existence
create or replace procedure p_check_role_exists(
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
begin
    select count(*) into a_row_count
    from meta_roles
    where role_name = lower(a_role_name);
    
    if a_row_count = 0 then
        raise_application_error(-20035,
                'A role with the specified name was not found (role_name = '
                || a_role_name
                || ') (#error_code = 20035)');
    end if;
end;

--go

--Function of deleting a role
create or replace procedure meta_delete_role(
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
    --Affected users
    a_affected_users meta_name_list;
begin
    p_check_role_exists(a_role_name);
    
    --Getting affected users
    a_affected_users := p_users_affected_by_principal(a_role_name);
    
    --Do not deleting the DBMS role
    
    --Deleting all role acl
    delete from meta_table_acl
    where principal_name = lower(a_role_name);
    
    --User enrollments and role enrollments
    -- will be deleted on cascade
    
    --Updating the metadata
    delete from meta_roles
    where role_name = lower(a_role_name);
    
    --Updating role users acl
    for i in 1..a_affected_users.count loop
        p_refresh_user_acl(a_affected_users(i));
    end loop;    
end;

--go

--Function of adding user in role
create or replace procedure meta_add_user_in_role(
    --User name
    a_user_name varchar2,
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
begin
    p_check_user_exists(a_user_name);
    p_check_role_exists(a_role_name);

    --Searching for user enrollment
    select count(*)
    into a_row_count from meta_user_roles
    where user_name = lower(a_user_name) 
    and container_role_name = lower(a_role_name);
    
    if a_row_count > 0 then
        raise_application_error(-20037,
                'Adding a user in a role twice is forbidden (role_name = '
                || a_role_name || ', user_name = '
                || a_user_name
                || ') (#error_code = 20037)');
    end if;
    
    --Enrolling user in role
    execute immediate 'grant ' || a_role_name || ' to ' || a_user_name;
    
    --Updating the metadata
    insert into meta_user_roles (user_name, container_role_name)
    values(lower(a_user_name), lower(a_role_name));
    
    --Refreshing user acl
    p_refresh_user_acl(a_user_name);
end;

--go

--Function of removing user from role
create or replace procedure meta_delete_user_from_role(
    --User name
    a_user_name varchar2,
    --Role name
    a_role_name varchar2)
as
    --Row count
    a_row_count number;
begin
    p_check_user_exists(a_user_name);
    p_check_role_exists(a_role_name);
    
    --Searching for user enrollment
    select count(*)
    into a_row_count from meta_user_roles
    where user_name = lower(a_user_name)
    and container_role_name = lower(a_role_name);
    
    if a_row_count = 0 then
        raise_application_error(-20039,
                'Specified user enrollment was not found (role_name = '
                || a_role_name || ', user_name = '
                || a_user_name
                || ') (#error_code = 20039)');
    end if;
    
    --Deleting user from role
    execute immediate 'revoke ' || a_role_name || ' from ' || a_user_name;

    --Updating the metadata
    delete from meta_user_roles
    where user_name = lower(a_user_name) 
    and container_role_name = lower(a_role_name);
    
    --Refreshing user acl
    p_refresh_user_acl(a_user_name);
end;

--go

--Function of adding role in role
create or replace procedure meta_add_role_in_role(
    --Role name
    a_role_name varchar2,
    --Container role name
    a_container_role_name varchar2)
as
    --Affected users
    a_affected_users meta_name_list := meta_name_list();
    --Row count
    a_row_count number;
begin
    p_check_role_exists(a_container_role_name);
    p_check_role_exists(a_role_name);
    
    --Searching for user enrollment
    select count(*)
    into a_row_count from meta_role_roles
    where role_name = lower(a_role_name) 
    and container_role_name = lower(a_container_role_name);
    
    if a_row_count > 0 then
        raise_application_error(-20038,
                'Adding a role in a role twice is forbidden (container_role_name = '
                || a_container_role_name || ', role_name = '
                || a_role_name
                || ') (#error_code = 20038)');
    end if;
    
    --Adding role in role
    execute immediate 'grant ' || a_container_role_name
    || ' to ' || a_role_name;
    
    --Updating the metadata
    insert into meta_role_roles(role_name, container_role_name)
    values(lower(a_role_name), lower(a_container_role_name));
    
    --Updating users acl
    a_affected_users := p_users_affected_by_principal(a_role_name);
    
     for i in 1..a_affected_users.count loop
        p_refresh_user_acl(a_affected_users(i));
    end loop;
end;

--go

--Function of removing role from role
create or replace procedure meta_delete_role_from_role(
    --Role name
    a_role_name varchar2,
    --Container role name
    a_container_role_name varchar2)
as
    --Row count
    a_row_count number;
    --Affected users
    a_affected_users meta_name_list;
begin
    p_check_role_exists(a_container_role_name);
    p_check_role_exists(a_role_name);
    
    --Searching for role enrollment
    select count(*) into a_row_count
    from meta_role_roles
    where role_name = lower(a_role_name)
    and container_role_name = lower(a_container_role_name);
    
    if a_row_count = 0 then
        raise_application_error(-20040,
                'Specified role enrollment was not found (container_role_name = '
                || a_container_role_name || ', role_name = '
                || a_role_name
                || ') (#error_code = 20040)');
    end if;
    
    execute immediate 'revoke ' || a_container_role_name
    || ' from ' || a_role_name;
    
    --Updating the metadata
    delete from meta_role_roles
    where  role_name = lower(a_role_name)
    and container_role_name = lower(a_container_role_name);
    
    --Updating users acl
    a_affected_users := p_users_affected_by_principal(a_role_name);
    
    for i in 1..a_affected_users.count loop
        p_refresh_user_acl(a_affected_users(i));
    end loop;

end;

--go